package com.wan.dao.bill;

import com.wan.dao.BaseDao;
import com.wan.pojo.Bill;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao{
    @Override
    public int getBillCount(Connection conn, String productName, int providerId,int isPayment) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int count = 0;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select count(1) as count from smbms_bill ");
            if (productName != null || providerId != 0 || isPayment != 0) {
                sql.append(" where ");
            }
            if (productName != null) {
                sql.append(" productName = ?");
                list.add(productName);
            }
            if (providerId != 0) {
                sql.append(" providerId = ?");
                list.add(providerId);
            }
            if (isPayment != 0) {
                sql.append(" isPayment = ?");
                list.add(isPayment);
            }
            System.out.println("getBillCount--sql-->"+sql.toString());
        }
        Object[] params = list.toArray();

        try {
            rs = BaseDao.execute(conn,pstm,rs, sql.toString(),params);
            while (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }

    @Override
    public List<Bill> getBillList(Connection conn, String productName, int providerId, int isPayment, int currentPageNo, int pageSize) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        ArrayList<Bill> billArrayList = null;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            billArrayList = new ArrayList<Bill>();
            sql.append("select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.providerId = p.id");
            if (productName != null) {
                sql.append(" and productName = ?");
                list.add(productName);
            }
            if (providerId != 0) {
                sql.append(" and providerId = ?");
                list.add(providerId);
            }
            if (isPayment != 0) {
                sql.append(" and isPayment = ?");
                list.add(isPayment);
            }
            //根据输入的当前页码和页面大小计算分页
            sql.append(" order by creationDate DESC limit ?,?");
            System.out.println("getBillList--list-->"+list);
            System.out.println("getBillList--sql-->"+sql.toString());
            System.out.println("getBillList--currentPageNo-->"+currentPageNo);
            System.out.println("getBillList--pageSize-->"+pageSize);
            System.out.println("---------------------------------------");
            currentPageNo = (currentPageNo - 1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
        }
        Object[] params = list.toArray();
        try {
            rs = BaseDao.execute(conn,pstm,rs, sql.toString(), params);
            while (rs.next()) {
                Bill bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setCreateBy(rs.getInt("createdBy"));
                bill.setCreationDate(rs.getDate("creationDate"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setModifyDate(rs.getDate("modifyDate"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                billArrayList.add(bill);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return billArrayList;
    }

    @Override
    public List<Integer> getProviderId(Connection conn) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Integer> getProviderIdList = null;
        if (conn != null) {
            sql = new StringBuffer();
            sql.append("select distinct providerId from smbms_bill");
        }
        try {
            rs = BaseDao.execute(conn,pstm,rs,sql.toString(),null);
            while (rs.next()) {
                getProviderIdList.add(Integer.valueOf(rs.getString("providerId")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return getProviderIdList;
    }

    @Override
    public int insertBill(Connection conn, Bill bill, int createdBy) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impactCount = 0;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("insert into smbms_bill(billCode," +
                    "productName,productUnit,productCount," +
                    "totalPrice,providerId,isPayment,creationDate,createdBy) values(?,?,?,?,?,?,?,?,?)");
            list.add(bill.getBillCode());
            list.add(bill.getProductName());
            list.add(bill.getProductUnit());
            list.add(bill.getProductCount());
            list.add(bill.getTotalPrice());
            list.add(bill.getProviderId());
            list.add(bill.getIsPayment());
            list.add(bill.getCreationDate());
            list.add(createdBy);
            System.out.println("insertBill--sqk=l--"+sql.toString());
        }
        Object[] params = list.toArray();
        try {
            impactCount = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return impactCount;
    }

    @Override
    public int deleteBill(Connection conn, int id) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impactedCount = 0;
        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("delete from smbms_bill where id = ?");
            list.add(id);
        }
        System.out.println("deleteBill--sql--"+sql.toString());
        System.out.println("deleteBill--list--"+list.toString());
        Object[] params = list.toArray();
        try {
            impactedCount = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return impactedCount;
    }

    @Override
    public Bill getBillById(Connection conn, int id) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        Bill bill = null;
        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.providerId = p.id and b.id = ?");
            list.add(id);
        }
        System.out.println("getBillById--sql---"+sql.toString());
        System.out.println("getBillById--list---"+list);
        Object[] params = list.toArray();
        try {
            rs = BaseDao.execute(conn,pstm,rs,sql.toString(),params);
            while (rs.next()) {
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setCreateBy(rs.getInt("createdBy"));
                bill.setCreationDate(rs.getDate("creationDate"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setCreationDate(rs.getDate("modifyDate"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return bill;
    }

    @Override
    public int updateBill(Connection conn, Bill bill, int modifyBy) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impactedCount = 0;
        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("update smbms_bill set productName = ?, productUnit = ?," +
                    " productCount = ? ,totalPrice = ?, providerId = ?, " +
                    "isPayment = ? ,modifyBy = ? ,modifyDate = ?  ");
            list.add(bill.getProductName());
            list.add(bill.getProductUnit());
            list.add(bill.getProductCount());
            list.add(bill.getTotalPrice());
            list.add(bill.getProviderId());
            list.add(bill.getIsPayment());
            list.add(modifyBy);
            list.add(bill.getModifyDate());
            if (bill.getId() != null) {
                sql.append(" where id = ?");
                list.add(bill.getId());
            }
        }
        System.out.println("updateBill--sql--"+sql.toString());
        System.out.println("updateBill--list--"+list);
        Object[] params = list.toArray();
        try {
            impactedCount = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return impactedCount;
    }
}
